﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using E.Enum;
using X.DALSqlServer;

namespace E.DAL
{
    public class ExamDAL
    {
        /// <summary>
        /// 获取试卷类型
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetExamCategory()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID,Name FROM E_ExamCategory";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取课程考试列表
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<ShortExamInfoModel> GetExamList(int userid)
        {
            IList<ShortExamInfoModel> list = new List<ShortExamInfoModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUserExamList", parms))
            {
                while (dr.Read())
                {
                    ShortExamInfoModel model = new ShortExamInfoModel();
                    model.ID = (int)dr["ID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.CourseCategoryName = dr["CourseCategoryName"].ToString();
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.EndTime = (DateTime)dr["EndTime"];
                    list.Add(model);
                }
                return list;
            }
        }
        
        /// <summary>
        /// 获取试卷模板列表
        /// </summary>
        /// <returns></returns>
        public IList<ExamDefaultModel> GetExamDefaultList()
        {
            IList<ExamDefaultModel> list = new List<ExamDefaultModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql("Select * from E_ExamDefault"))
            {
                while (dr.Read())
                {
                    ExamDefaultModel seim = new ExamDefaultModel();
                    seim.id = (int)dr["ID"];
                    seim.CategoryIDArr = dr["CategoryIDArr"].ToString();
                    seim.QuestionQuantityArr = dr["QuestionQuantityArr"].ToString();
                    seim.DefaultName = dr["DefaultName"].ToString();
                    seim.RequiredQuestionQuantityArr = dr["RequiredQuestionQuantityArr"].ToString();
                    list.Add(seim);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取考试列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ExamListModel GetExamListByCondition(SearchExamModel searchModel, int pageSize, int pageIndex)
        {
            ExamListModel listModel = new ExamListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", searchModel.Title),
                new SqlParameter("@CategoryID", (int)searchModel.CategoryID),
                new SqlParameter("@CourseCertificateID", searchModel.CourseCertificateID),
                new SqlParameter("@StartFromTime", searchModel.StartFromTime),
                new SqlParameter("@StartToTime", searchModel.StartToTime),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortExamInfoModel> list = new List<ShortExamInfoModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getExamListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortExamInfoModel seim = new ShortExamInfoModel();
                        seim.ID = (int)dr["ID"];
                        seim.CourseCertificateID = (int)dr["CourseCertificateID"];
                        seim.CategoryID = (EnumExamCategory)dr["CategoryID"];
                        seim.Title = dr["Title"].ToString();
                        seim.StartTime = (DateTime)dr["StartTime"];
                        list.Add(seim);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }
        

        /// <summary>
        /// 获取试卷模板列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ExamDefaultListModel GetExamDefaultListByCondition(SearchExamModel searchModel, int pageSize, int pageIndex)
        {
            ExamDefaultListModel listModel = new ExamDefaultListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", searchModel.Title),
                new SqlParameter("@CategoryID", (int)searchModel.CategoryID),
                new SqlParameter("@CourseCertificateID", searchModel.CourseCertificateID),
                new SqlParameter("@StartFromTime", searchModel.StartFromTime),
                new SqlParameter("@StartToTime", searchModel.StartToTime),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ExamDefaultModel> list = new List<ExamDefaultModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getExamDefaultListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ExamDefaultModel seim = new ExamDefaultModel();
                        seim.id = (int)dr["ID"];
                        seim.CategoryIDArr = dr["CategoryIDArr"].ToString();
                        seim.QuestionQuantityArr = dr["QuestionQuantityArr"].ToString();
                        seim.DefaultName = dr["DefaultName"].ToString();
                        seim.RequiredQuestionQuantityArr = dr["RequiredQuestionQuantityArr"].ToString();
                        list.Add(seim);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }
        #region wnan
        public PaperInspectListModel GetPaperInspectListModelByCondition(SearchExamModel searchModel, int pageSize, int pageIndex)
        {
            PaperInspectListModel listModel = new PaperInspectListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@userName", searchModel.userName),
                new SqlParameter("@userEmail", searchModel.email),
                new SqlParameter("@userLoginId", searchModel.loginid),
                new SqlParameter("@areaId", searchModel.AreaID),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@CourseCertificateID", searchModel.CourseCertificateID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@Title", searchModel.Title),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<PaperInspectModel> list = new List<PaperInspectModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getExamListByPaperInspect", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        PaperInspectModel seim = new PaperInspectModel();
                        seim.ExamID = (int)dr["ID"];
                        seim.UserID = (int)dr["userid"];
                        seim.ExamTitle = dr["Title"].ToString();
                        seim.UserName = dr["username"].ToString();
                        seim.CourseName = dr["coursename"].ToString();
                        seim.ProjectName = dr["projectname"].ToString();
                        seim.TestLogId = (int)dr["testlogid"];
                        seim.TestId = (int)dr["testid"];
                        list.Add(seim);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        public QuestionStaticsListModel GetQuestionStaticListModelByCondition(SearchExamModel searchModel, int pageSize, int pageIndex)
        {

            QuestionStaticsListModel listModel = new QuestionStaticsListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@courseId", searchModel.CourseCertificateID),
                new SqlParameter("@certificateId", searchModel.CertificateID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<QuestionStaticsModel> list = new List<QuestionStaticsModel>();
            QuestionStaticsModel qsm;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getTestStaticsReport", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        qsm= new QuestionStaticsModel();
                        qsm.Id = (int)dr["ID"];
                        qsm.QuestionType = dr["QuestionType"].ToString();
                        qsm.QuestionTitle = dr["QuestionTitle"].ToString();
                        qsm.QuestionOption = dr["QuestionOption"].ToString();
                        qsm.RightAnswer = dr["RightAnswer"].ToString();
                        qsm.QuestionCount = (int)dr["QuestionCount"];
                        qsm.RightPercent = dr["RightPercent"].ToString();
                        list.Add(qsm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 管理员可统计每个老师所开设班级考试平均成绩，学习人数
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public AverageStaticsListModel GetAverageScoreStaticListModelByCondition(SearchExamModel searchModel, int pageSize, int pageIndex)
        {

            AverageStaticsListModel listModel = new AverageStaticsListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@courseId", searchModel.CourseCertificateID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<AverageStaticsModel> list = new List<AverageStaticsModel>();
            AverageStaticsModel qsm;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("usp_getAverageScoreByClass", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        qsm = new AverageStaticsModel();
                        qsm.CourseName = dr["Coursename"].ToString();
                        qsm.ClassCode = dr["ClassName"].ToString();
                        qsm.StudentNumber = dr["StudentNumber"].ToString();
                        qsm.AverageScore = dr["AverageScore"].ToString();
                        list.Add(qsm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }
        #endregion

        /// <summary>
        /// 获取课程ID通过考试ID
        /// </summary>
        /// <param name="examid"></param>
        /// <returns></returns>
        public int GetCourseIDByExamID(int examid)
        {
            int i = -1;
            string sql = "SELECT ISNULL(CourseCertificateID,-1) AS CourseCertificateID FROM [dbo].[E_Exam] WHERE [ID]=" + examid.ToString();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    i = (int)dr["CourseCertificateID"];
                }
            }
            return i;
        }


        /// <summary>
        /// 获取试卷信息（课程）
        /// </summary>
        /// <param name="id"></param>
        /// <param name="categoryid"></param>
        /// <returns></returns>
        public ExamInfoModel GetExam2CourseInfo(int id)
        {
            string sql = "SELECT a.ID, a.Title, a.CategoryID, a.CourseCertificateID, a.StartTime, a.EndTime, a.ExamTime, a.TotalScore, a.PassScore, b.Name AS CourseCertificateName, a.CertificateID, a.QuestionIDArray, a.IsRandom FROM E_Exam AS a LEFT OUTER JOIN E_Dict_Course AS b ON a.CourseCertificateID = b.ID WHERE a.ID = " + id;
            return GetExamInfo(id, sql);
        }

        /// <summary>
        /// 获取试卷信息（认证）
        /// </summary>
        /// <param name="id"></param>
        /// <param name="categoryid"></param>
        /// <returns></returns>
        public ExamInfoModel GetExam2CertificateInfo(int id)
        {
            string sql = "SELECT a.ID, a.Title, a.CategoryID, a.CourseCertificateID, a.StartTime, a.EndTime, a.ExamTime, a.TotalScore, a.PassScore, b.Name AS CourseCertificateName, a.CertificateID, a.QuestionIDArray, a.IsRandom FROM E_Exam AS a LEFT OUTER JOIN E_Dict_Certificate AS b ON a.CourseCertificateID = b.ID WHERE a.ID = " + id;

            return GetExamInfo(id, sql);
        }

        /// <summary>
        /// 获取试卷信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private ExamInfoModel GetExamInfo(int id, string sql)
        {
            ExamInfoModel model = new ExamInfoModel();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.Title = dr["Title"].ToString();
                    model.CategoryID = (EnumExamCategory)dr["CategoryID"];
                    model.CourseCertificateID = (int)dr["CourseCertificateID"];
                    model.CertificateID = (int)dr["CertificateID"];
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.EndTime = (DateTime)dr["EndTime"];
                    model.ExamTime = (int)dr["ExamTime"];
                    model.TotalScore = (int)dr["TotalScore"];
                    model.PassScore = (int)dr["PassScore"];
                    model.CourseCertificateName = dr["CourseCertificateName"].ToString();
                    model.AreaList = GetExamAreaList(id);
                    model.QuestionIDArray = dr["QuestionIDArray"].ToString();
                    model.IsRandom = (int)dr["IsRandom"];

                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取考试信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ExamInfoModel GetExamInfoByID(int id)
        {
            string sql = "SELECT ID,Title,StartTime,Name,ExamTime,QuestionIDArray, IsRandom FROM v_examunion WHERE ID = " + id;
            ExamInfoModel model = new ExamInfoModel();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.Title = dr["Title"].ToString();
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.ExamTime = (int)dr["ExamTime"];
                    model.CourseCertificateName = dr["Name"].ToString();
                    model.QuestionIDArray = dr["QuestionIDArray"].ToString();
                    model.IsRandom = (int)dr["IsRandom"];
                }

                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取试卷地区列表
        /// </summary>
        /// <param name="examid"></param>
        /// <returns></returns>
        public Dictionary<int, string> GetExamAreaList(int examid)
        {
            Dictionary<int, string> list = new Dictionary<int,string>();
            string sql = "SELECT b.ID, b.Name FROM E_Exam2Area AS a LEFT OUTER JOIN E_Dict_Area AS b ON a.AreaID = b.ID WHERE a.ExamID = " + examid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 检查学员是否已经参加过考试
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="examid"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public int GetTestInfo(int userid, int examid, EnumExamCategory category)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@ExamID", examid),
                new SqlParameter("@CategoryID", (int)category)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_checkTestInfo", parms))
            {
                int testid = -1;
                if (dr.Read())
                {
                    testid = (int)dr[0];
                }

                return testid;
            }
        }

        /// <summary>
        /// 获取认证考试ID
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="areaid"></param>
        /// <returns></returns>
        public IList<int> GetCertificateTestID(int userid, int areaid)
        {
            IList<int> list = new List<int>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@AreaID", areaid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getCertificateTest", parms))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0]);
                }
                return list;
            }            
        }

        /// <summary>
        /// 添加试卷信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddExam(ExamInfoModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", model.Title),
                new SqlParameter("@CourseCertificateID", model.CourseCertificateID),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@StartTime", model.StartTime),
                new SqlParameter("@EndTime", model.EndTime),
                new SqlParameter("@TotalScore", model.TotalScore),
                new SqlParameter("@PassScore", model.PassScore),
                new SqlParameter("@CertificateID", model.CertificateID),
                new SqlParameter("@ExamTime", model.ExamTime),
                new SqlParameter("@QuestionIDArray",model.QuestionIDArray),
                new SqlParameter("@IsRandom",model.IsRandom)
            };

            try
            {
                string sql = "INSERT INTO E_Exam(Title,CategoryID,CourseCertificateID,StartTime,EndTime,ExamTime,TotalScore,PassScore,CertificateID,QuestionIDArray,IsRandom)VALUES(@Title,@CategoryID,@CourseCertificateID,@StartTime,@EndTime,@ExamTime,@TotalScore,@PassScore,@CertificateID,@QuestionIDArray,@IsRandom);SELECT @@IDENTITY;";
                int id = Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql, parms));
                ArrayList sqllist = new ArrayList();
                ExamAreaHandle(id, model.AreaStrList, sqllist);
                if (model.IsRandom == 1)
                {
                    ExamSettingHandle(id, model.DataSettingList, sqllist);
                }
                int rst = SqlHelper.ExecuteSqlTran(sqllist);
                return rst;
            }
            catch (SqlException sqex)
            {
                return -1;
            }
            catch (Exception e)
            {
                return -2;
            }

        }

        /// <summary>
        /// 更新试卷信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateExam(ExamInfoModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Title", model.Title),
                new SqlParameter("@CourseCertificateID", model.CourseCertificateID),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@StartTime", model.StartTime),
                new SqlParameter("@EndTime", model.EndTime),
                new SqlParameter("@TotalScore", model.TotalScore),
                new SqlParameter("@PassScore", model.PassScore),
                new SqlParameter("@CertificateID", model.CertificateID),
                new SqlParameter("@ExamTime", model.ExamTime),
                new SqlParameter("@QuestionIDArray",model.QuestionIDArray),
                new SqlParameter("@IsRandom",model.IsRandom)
            };
            try
            {
                string sql = "UPDATE E_Exam SET Title = @Title, CategoryID = @CategoryID, CourseCertificateID = @CourseCertificateID, StartTime = @StartTime, EndTime = @EndTime, ExamTime = @ExamTime, TotalScore = @TotalScore, PassScore = @PassScore, CertificateID = @CertificateID,QuestionIDArray = @QuestionIDArray,IsRandom = @IsRandom WHERE ID = @ID";
                int rst = SqlHelper.ExecuteNonQueryBySql(sql, parms);
                ArrayList sqllist = new ArrayList();
                ExamAreaHandle(model.ID, model.AreaStrList, sqllist);
                if (model.IsRandom == 1)
                {
                    ExamSettingHandle(model.ID, model.DataSettingList, sqllist);
                }
                rst += SqlHelper.ExecuteSqlTran(sqllist);
                return rst;
            }
            catch (SqlException sqex)
            {
                return -1;
            }
            catch (Exception e)
            {
                return -2;
            }

        }

        /// <summary>
        /// 添加试卷模板
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddExamDefault(ExamDefaultModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@DefaultName", model.DefaultName),
                new SqlParameter("@CategoryIDArr", model.CategoryIDArr),
                new SqlParameter("@QuestionQuantityArr", model.QuestionQuantityArr),
                new SqlParameter("@RequiredQuestionQuantityArr", model.RequiredQuestionQuantityArr)
            };
            try
            {
                string sql = "INSERT INTO E_ExamDefault(DefaultName,CategoryIDArr,QuestionQuantityArr,RequiredQuestionQuantityArr)VALUES(@DefaultName,@CategoryIDArr,@QuestionQuantityArr,@RequiredQuestionQuantityArr);";
                int rst = SqlHelper.ExecuteNonQueryBySql(sql, parms);
                return rst;
            }
            catch (SqlException sqex)
            {
                return -1;
            }
            catch (Exception e)
            {
                return -2;
            }

        }

        /// <summary>
        /// 更新试卷模板
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateExamDefault(ExamDefaultModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.id),
                new SqlParameter("@DefaultName", model.DefaultName),
                new SqlParameter("@CategoryIDArr", model.CategoryIDArr),
                new SqlParameter("@QuestionQuantityArr", model.QuestionQuantityArr),
                new SqlParameter("@RequiredQuestionQuantityArr", model.RequiredQuestionQuantityArr)
            };
            try
            {
                string sql = "UPDATE E_ExamDefault SET DefaultName = @DefaultName, CategoryIDArr = @CategoryIDArr, QuestionQuantityArr = @QuestionQuantityArr, RequiredQuestionQuantityArr = @RequiredQuestionQuantityArr WHERE ID = @ID";
                int rst = SqlHelper.ExecuteNonQueryBySql(sql, parms);
                return rst;
            }
            catch (SqlException sqex)
            {
                return -1;
            }
            catch (Exception e)
            {
                return -2;
            }

        }

        /// <summary>
        /// 获取考试模板
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ExamDefaultModel GetExamDefaultByID(int id)
        {
            string sql = "SELECT [id],[DefaultName],[CategoryIDArr],[QuestionQuantityArr],[RequiredQuestionQuantityArr] FROM [E_ExamDefault] WHERE ID = " + id;
            ExamDefaultModel model = new ExamDefaultModel();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.id = (int)dr["id"];
                    model.DefaultName = dr["DefaultName"].ToString();
                    model.CategoryIDArr = dr["CategoryIDArr"].ToString();
                    model.QuestionQuantityArr = dr["QuestionQuantityArr"].ToString();
                    model.RequiredQuestionQuantityArr = dr["RequiredQuestionQuantityArr"].ToString();
                }
                else
                    model.id = -1;
                return model;
            }
        }
        #region 试卷相关处理

        /// <summary>
        /// 试卷地区处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="areaid"></param>
        /// <returns></returns>
        public void ExamAreaHandle(int id, string[] areaid, ArrayList sqllist)
        {
            sqllist.Add("DELETE FROM E_Exam2Area WHERE ExamID = " + id);
            foreach (string s in areaid)
            {
                sqllist.Add("INSERT INTO E_Exam2Area(ExamID,AreaID)VALUES(" + id + "," + s + ");");
            }
        }

        /// <summary>
        /// 试卷设置处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="settingModel"></param>
        /// <param name="sqllist"></param>
        public void ExamSettingHandle(int id, IList<SettingModelForDataHandle> settingModel, ArrayList sqllist)
        {
            sqllist.Add("DELETE FROM E_ExamSetting WHERE ExamID = " + id);
            foreach (SettingModelForDataHandle smfdh in settingModel)
            {
                sqllist.Add("INSERT INTO E_ExamSetting(ExamID,CourseID,QuestionCategoryID,QuestionQuantity,RequiredQuestionQuantity)VALUES(" + id + "," + smfdh.CourseID + "," + smfdh.QuestionCategoryID + "," + smfdh.QuestionQuantity + "," + smfdh.RequiredQuestionQuantity + ");");
            }
        }

        #endregion

        /// <summary>
        /// 获取成绩列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ScoreListModel SearchScoreByCondition(SearchScoreModel searchModel, int pageSize, int pageIndex)
        {
            ScoreListModel listModel = new ScoreListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", searchModel.LoginID),
                new SqlParameter("@UserName", searchModel.UserName),
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@CourseCategoryID", searchModel.CourseCategoryID),
                new SqlParameter("@StartFromDate", searchModel.StartFromDate),
                new SqlParameter("@StartToDate", searchModel.StartToDate),
                new SqlParameter("@EntryFromDate", searchModel.EntryFromDate),
                new SqlParameter("@EntryToDate", searchModel.EntryToDate),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortScoreModel> list = new List<ShortScoreModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getScoreListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortScoreModel ssm = new ShortScoreModel();
                        //ssm.ID = (int)dr["ID"];
                        ssm.LoginID = dr["LoginID"].ToString();
                        ssm.UserID = (int)dr["ID"];
                        ssm.UserName = dr["UserName"].ToString();
                        ssm.Education = dr["Education"].ToString();
                        //ssm.CourseCertificateName = dr["CourseCertificateName"].ToString();
                        //ssm.Score = (int)dr["Score"];
                        //ssm.TestID = (int)dr["TestID"];
                        //ssm.ExamID = (int)dr["ExamID"];
                        ssm.Gender = (bool)dr["Gender"];
                        //ssm.CategoryID = (int)dr["CategoryID"];
                        list.Add(ssm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取所有成绩数据
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public IList<ShortScoreModel> GetAllScore(SearchScoreModel searchModel)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", searchModel.LoginID),
                new SqlParameter("@UserName", searchModel.UserName),
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@CourseCategoryID", searchModel.CourseCategoryID),
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@StartFromDate", searchModel.StartFromDate),
                new SqlParameter("@StartToDate", searchModel.StartToDate),
                new SqlParameter("@EntryFromDate", searchModel.EntryFromDate),
                new SqlParameter("@EntryToDate", searchModel.EntryToDate),
                new SqlParameter("@AreaID", searchModel.AreaID)
            };
            IList<ShortScoreModel> list = new List<ShortScoreModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getAllScore", parms))
            {
                while (dr.Read())
                {
                    //ShortScoreModel ssm = new ShortScoreModel();
                    //ssm.ID = (int)dr["ID"];
                    //ssm.LoginID = dr["LoginID"].ToString();
                    //ssm.UserName = dr["UserName"].ToString();
                    //ssm.Education = dr["Education"].ToString();
                    //ssm.Score = (int)dr["Score"];
                    //ssm.Gender = (bool)dr["Gender"];
                    //ssm.CourseCertificateName = GetCourseCertificateName((EnumExamCategory)dr["CategoryID"], (int)dr["CourseCertificateID"]);
                    //list.Add(ssm);
                    ShortScoreModel ssm = new ShortScoreModel();
                    ssm.LoginID = dr["LoginID"].ToString();
                    ssm.UserID = (int)dr["ID"];
                    ssm.UserName = dr["UserName"].ToString();
                    ssm.Education = dr["Education"].ToString();
                    ssm.Gender = (bool)dr["Gender"];
                    list.Add(ssm);
                }
                return list;
            }
        }

        private string GetCourseCertificateName(EnumExamCategory category, int courseCertificateID)
        {
            string sql = "";
            switch (category)
            { 
                case EnumExamCategory.CourseExam:
                    sql = "SELECT Name From E_Dict_Course WHERE ID = " + courseCertificateID;
                    break;
                case EnumExamCategory.CertificateExam:
                    sql = "SELECT Name From E_Dict_Certificate WHERE ID = " + courseCertificateID;
                    break;
                default: break;
            }
            return SqlHelper.ExecuteScalarBySql(sql).ToString(); ;
        }

        /// <summary>
        /// 获取试卷题目设置
        /// </summary>
        /// <param name="examid"></param>
        /// <returns></returns>
        public IList<ExamSettingModel> GetExamQuestionSetting(int examid)
        {
            IList<ExamSettingModel> list = new List<ExamSettingModel>();
            string sql = "SELECT DISTINCT a.QuestionCategoryID, b.Name AS QuestionCategoryName FROM E_ExamSetting AS a LEFT OUTER JOIN E_QuestionCategory AS b ON a.QuestionCategoryID = b.ID WHERE (a.ExamID = " + examid + ") ORDER BY a.QuestionCategoryID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    ExamSettingModel settingModel = new ExamSettingModel();
                    settingModel.QuestionCategoryID = (int)dr["QuestionCategoryID"];
                    settingModel.QuestionCategoryName = dr["QuestionCategoryName"].ToString();
                    settingModel.SettingList = GetExamDetailSetting(examid, settingModel.QuestionCategoryID);
                    list.Add(settingModel);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取试卷简单设置
        /// </summary>
        /// <param name="examid"></param>
        /// <returns></returns>
        public IList<ShortExamSettingModel> GetExamShortSetting(int examid)
        {
            IList<ShortExamSettingModel> list = new List<ShortExamSettingModel>();
            string sql = "SELECT CourseID, QuestionCategoryID, QuestionQuantity, RequiredQuestionQuantity FROM E_ExamSetting WHERE ExamID = " + examid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    ShortExamSettingModel model = new ShortExamSettingModel();
                    model.CourseID = (int)dr["CourseID"];
                    model.QuestionCategoryID = (int)dr["QuestionCategoryID"];
                    model.QuestionQuantity = (int)dr["QuestionQuantity"];
                    model.RequiredQuestionQuantity = (int)dr["RequiredQuestionQuantity"];
                    list.Add(model);
                }
                return list;
            }

        }

        /// <summary>
        /// 获取试卷各题型设置
        /// </summary>
        /// <param name="examid"></param>
        /// <param name="questionCategoryID"></param>
        /// <returns></returns>
        public IList<ExamDetailSettingModel> GetExamDetailSetting(int examid, int questionCategoryID)
        {
            IList<ExamDetailSettingModel> list = new List<ExamDetailSettingModel>();
            string sql = "SELECT ID, CourseID, QuestionQuantity, RequiredQuestionQuantity, CourseName FROM v_examsetting WHERE (ExamID = " + examid + ") AND (QuestionCategoryID = " + questionCategoryID + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    ExamDetailSettingModel model = new ExamDetailSettingModel();
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.QuestionQuantity = (int)dr["QuestionQuantity"];
                    model.RequiredQuestionQuantity = (int)dr["RequiredQuestionQuantity"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取课程考试列表（考试列表已经写死在存储过程中，以后再优化）
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="areaid"></param>
        /// <returns></returns>
        public IList<ShortExamInfoModel> GetCourseExamList(int userid, int areaid)
        {
            IList<ShortExamInfoModel> list = new List<ShortExamInfoModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@AreaID", areaid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getCourseExamList", parms))
            {
                while (dr.Read())
                {
                    ShortExamInfoModel model = new ShortExamInfoModel();
                    model.ID = (int)dr["ID"];
                    model.Title = dr["Title"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.CourseCategoryName = dr["CourseCategoryName"].ToString();
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.EndTime = (DateTime)dr["EndTime"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 删除题目
        /// </summary>
        /// <param name="examid"></param>
        /// <returns></returns>
        public int DelExam(string[] examid)
        {
            string sql = "DELETE FROM E_Exam WHERE " + DALUtils.GetInCondition("ID", examid) + "; DELETE FROM E_Exam2Area WHERE " + DALUtils.GetInCondition("ExamID", examid) + "; DELETE FROM E_ExamSetting WHERE " + DALUtils.GetInCondition("ExamID", examid);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }
        /// <summary>
        /// 删除模板
        /// </summary>
        /// <param name="examid"></param>
        /// <returns></returns>
        public int DelExamDefault(string[] examid)
        {
            string sql = "DELETE FROM E_ExamDefault WHERE " + DALUtils.GetInCondition("ID", examid);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 获取用户课程考试成绩
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<ShortScoreModel> GetUserCourseScoreList(int userid)
        {
            IList<ShortScoreModel> list = new List<ShortScoreModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUserCourseScoreList", parms))
            {
                while (dr.Read())
                {
                    ShortScoreModel model = new ShortScoreModel();
                    model.ID = (int)dr["ID"];
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.Score = (int)dr["Score"];
                    model.CourseCertificateName = dr["CourseName"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        #region 用户成绩统计

        /// <summary>
        /// 获取用户口试与平时成绩
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public int GetSetScore(int userid,int category)
        {
            string sql = "SELECT Score FROM E_ScoreLog WHERE UserID = " + userid + " AND ScoreCategoryID = " + category;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    return (int)dr["Score"];
                }
                else
                    return 0;
            }
        }

        /// <summary>
        /// 获取输入成绩列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public IList<ShortScoreInputModel> GetScoreInputList(SearchScoreInputModel searchModel)
        {
            IList<ShortScoreInputModel> list = new List<ShortScoreInputModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@RecordDisplayQuantity", searchModel.RecordDisplayQuantity),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@ScoreCategoryID", searchModel.ScoreCategoryID),
                new SqlParameter("@IsInputed", searchModel.IsInputed),
                new SqlParameter("@EntryFromDate", searchModel.EntryFromDate),
                new SqlParameter("@EntryToDate", searchModel.EntryToDate),
                new SqlParameter("@LoginID", searchModel.LoginID),
                new SqlParameter("@UserName", searchModel.UserName)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUserScoreInputList", parms))
            {
                while (dr.Read())
                {
                    ShortScoreInputModel model = new ShortScoreInputModel();
                    model.UserID = (int)dr["ID"];
                    model.LoginID = dr["LoginID"].ToString();
                    model.UserName = dr["UserName"].ToString();
                    model.CertificateID = (int)dr["CurrentCertificateID"];
                    model.Score = dr["Score"] is DBNull ? 0 : (int)dr["Score"];
                    model.ScoreCategoryID = dr["ScoreCategoryID"] is DBNull ? -1 : (int)dr["ScoreCategoryID"];
                    model.ScoreCategoryName = dr["CategoryName"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 学员成绩输入
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UserInputScoreHandle(InputModel model)
        {
            ArrayList sqlList = new ArrayList();
            sqlList.Add("DELETE FROM E_ScoreLog WHERE UserID IN (" + model.UserIDStr + ");");
            for (int i = 0; i < model.UserIDArray.Length; i++) {
                sqlList.Add("INSERT INTO E_ScoreLog(UserID,ScoreCategoryID,Score)VALUES(" + model.UserIDArray[i] + "," + model.ScoreCategoryIDArray[i] + "," + model.ScoreArray[i] + ")");
            }
            return SqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 获取用户认证考试成绩
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="areaid"></param>
        /// <returns></returns>
        public ShortScoreModel GetCertificateScore(int userid, int areaid)
        {
            ShortScoreModel model = new ShortScoreModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@AreaID", areaid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUserCertificateScore", parms))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.Score = (int)dr["Score"];
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 获取用户所有课程成绩
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<int> GetUserCourseScore(int userid)
        {
            IList<int> list = new List<int>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUserCourseScoreList", parms))
            {
                while (dr.Read())
                {
                    list.Add((int)dr["Score"]);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取用户输入成绩信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public ScoreLogModel GetUserInputScoreInfo(int userid)
        {
            string sql = "SELECT a.ID, a.LoginID, b.UserName, c.Name AS CertificateName FROM E_User AS a INNER JOIN E_UserInfo AS b ON a.ID = b.UserID INNER JOIN E_Dict_Certificate AS c ON a.CurrentCertificateID = c.ID WHERE a.ID = " + userid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                ScoreLogModel model = new ScoreLogModel();
                if (dr.Read())
                {
                    model.UserID = (int)dr["ID"];
                    model.LoginID = dr["LoginID"].ToString();
                    model.UserName = dr["UserName"].ToString();
                    model.CertificateName = dr["CertificateName"].ToString();
                    model.ScoreLog = GetUserScoreLog(userid);
                }
                else
                    model.UserID = -1;
                return model;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        //public IDictionary<EnumScoreCategory, int> GetUserScoreLog(int userid)
        //{
        //    IDictionary<EnumScoreCategory, int> list = new Dictionary<EnumScoreCategory, int>();
        //    IList<EnumScoreCategory> categoryList = EnumClass.GetInputScoreCategoryIList();
        //    foreach (EnumScoreCategory category in categoryList)
        //    {
        //        string sql = "SELECT Score FROM E_ScoreLog WHERE UserID = " + userid + " AND ScoreCategoryID = " + (int)category;
        //        object rst = SqlHelper.ExecuteScalarBySql(sql);
        //        int score = rst == null ? 0 : Convert.ToInt32(rst);
        //        list.Add(category, score);
        //    }
        //    return list;
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<ScoreModel> GetUserScoreLog(int userid)
        {
            IDictionary<int, string> clist = GetInputScoreCategoryList();
            IList<ScoreModel> list = new List<ScoreModel>();
            foreach (KeyValuePair<int,string> kvp in clist)
            {
                string sql = "SELECT Score FROM E_ScoreLog WHERE UserID = " + userid + " AND ScoreCategoryID = " + kvp.Key;
                object rst = SqlHelper.ExecuteScalarBySql(sql);
                int score = rst == null ? 0 : Convert.ToInt32(rst);
                list.Add(new ScoreModel() { ScoreCategoryID = kvp.Key, CategoryName = kvp.Value, Score = score });
            }
            return list;
            //string sql = "SELECT a.ScoreCategoryID, a.Score, b.Name AS CategoryName FROM E_ScoreLog AS a INNER JOIN E_Dict_ScoreScale AS b ON a.ScoreCategoryID = b.ID WHERE a.UserID = " + userid;
            //using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            //{
            //    while (dr.Read())
            //    {
            //        ScoreModel model = new ScoreModel();
            //        model.ScoreCategoryID = (int)dr["ScoreCategoryID"];
            //        model.CategoryName = dr["CategoryName"].ToString();
            //        model.Score = (int)dr["Score"];
            //        list.Add(model);
            //    }
            //    return list;
            //}
        }

        #endregion

        /// <summary>
        /// 获取测试的开始结束时间与考试时间
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ShortExamInfoModel GetShortExamInfo(int id)
        {
            ShortExamInfoModel model = new ShortExamInfoModel();
            string sql = "SELECT StartTime,EndTime,ExamTime FROM E_Exam WHERE ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.EndTime = (DateTime)dr["EndTime"];
                    model.ExamTime = (int)dr["ExamTime"];
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取用户进入考试时间
        /// </summary>
        /// <param name="examid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public ShortTestModel GetUserTestStartTime(int examid, int userid)
        {
            string sql = "SELECT ID,StartTime,IsOver FROM v_test WHERE (ExamID = " + examid + ") AND (UserID = " + userid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                ShortTestModel info = new ShortTestModel();
                if (dr.Read())
                {
                    info.ID = (int)dr[0];
                    info.StartTime = (DateTime)dr[1];
                    info.IsOver = (bool)dr[2];
                }
                else
                    info.ID = -1;
                return info;
            }
        }

        /// <summary>
        /// 更新考试信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateTestInfo(TestModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", model.UserID),
                new SqlParameter("@ExamID", model.ExamID),
                new SqlParameter("@AnswerArray", model.AnswerArray),
                new SqlParameter("@SubjectiveQuestionAnswer", model.SubjectiveQuestionAnswer),
                new SqlParameter("@SubmitTime", model.SubmitTime),
                new SqlParameter("@Score", model.Score)
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_updateTestInfo", parms);
        }

        #region Score Category

        /// <summary>
        /// 获取成绩比例列表
        /// </summary>
        /// <returns></returns>
        public IList<ScoreCategoryModel> GetScoreCategoryList()
        {
            IList<ScoreCategoryModel> list = new List<ScoreCategoryModel>();
            string sql = "SELECT ID,Name,Scale,IsInput,IsCourse FROM E_Dict_ScoreScale";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    ScoreCategoryModel model = new ScoreCategoryModel();
                    model.ID = (int)dr["ID"];
                    model.Name = dr["Name"].ToString();
                    model.Scale = (int)dr["Scale"];
                    model.IsInput = (bool)dr["IsInput"];
                    model.IsCourse = (bool)dr["IsCourse"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取输入成绩类别列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetInputScoreCategoryList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID,Name FROM E_Dict_ScoreScale WHERE IsInput = 1";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr["ID"], dr["Name"].ToString());
                }
                return list;
            }
        }

        #endregion

        #region 内训

        /// <summary>
        /// 获取内训课程考试列表
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<ShortExamInfoModel> GetInternalCourseExamList(int userid)
        {
            IList<ShortExamInfoModel> list = new List<ShortExamInfoModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getCourseExamList", parms))
            {
                while (dr.Read())
                {
                    ShortExamInfoModel model = new ShortExamInfoModel();
                    model.ID = (int)dr["ID"];
                    model.Title = dr["Title"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.EndTime = (DateTime)dr["EndTime"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训用户输入成绩信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public ScoreLogModel GetInternalUserInputScoreInfo(int userid)
        {
            string sql = "SELECT a.ID, a.LoginID, b.UserName, c.Name AS CertificateName FROM E_User AS a INNER JOIN E_UserInfo AS b ON a.ID = b.UserID INNER JOIN E_Dict_Certificate AS c ON a.CurrentCertificateID = c.ID WHERE a.ID = " + userid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                ScoreLogModel model = new ScoreLogModel();
                if (dr.Read())
                {
                    model.UserID = (int)dr["ID"];
                    model.LoginID = dr["LoginID"].ToString();
                    model.UserName = dr["UserName"].ToString();
                    model.CertificateName = dr["CertificateName"].ToString();
                    model.ScoreLog = GetInternalUserScoreLog(userid);
                }
                else
                    model.UserID = -1;
                return model;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<ScoreModel> GetInternalUserScoreLog(int userid)
        {
            IDictionary<int, string> clist = GetInternalScoreCategoryList();
            IList<ScoreModel> list = new List<ScoreModel>();
            foreach (KeyValuePair<int, string> kvp in clist)
            {
                string sql = "SELECT Score FROM E_Internal_Score WHERE UserID = " + userid + " AND CategoryID = " + kvp.Key;
                object rst = SqlHelper.ExecuteScalarBySql(sql);
                int score = rst == null ? 0 : Convert.ToInt32(rst);
                list.Add(new ScoreModel() { ScoreCategoryID = kvp.Key, CategoryName = kvp.Value, Score = score });
            }
            return list;
        }

        /// <summary>
        /// 学员成绩输入
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InternalUserInputScoreHandle(InputModel model)
        {
            ArrayList sqlList = new ArrayList();
            sqlList.Add("DELETE FROM E_Internal_Score WHERE UserID IN (" + model.UserIDStr + ");");
            for (int i = 0; i < model.UserIDArray.Length; i++)
            {
                sqlList.Add("INSERT INTO E_Internal_Score(UserID,CategoryID,Score)VALUES(" + model.UserIDArray[i] + "," + model.ScoreCategoryIDArray[i] + "," + model.ScoreArray[i] + ")");
            }
            return SqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 获取内训课程考试列表（补考）
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<ShortExamInfoModel> GetInternalSecCourseExamList(int userid)
        {
            IList<ShortExamInfoModel> list = new List<ShortExamInfoModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getSecCourseExamList", parms))
            {
                while (dr.Read())
                {
                    ShortExamInfoModel model = new ShortExamInfoModel();
                    model.ID = (int)dr["ID"];
                    model.Title = dr["Title"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.EndTime = (DateTime)dr["EndTime"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取原考试ID
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="examid"></param>
        /// <returns></returns>
        public int GetOriginalTestID(int userid, int examid)
        {
            string sql = "SELECT a.ID FROM E_Test AS a INNER JOIN E_TestLog AS b ON a.ID = b.TestID WHERE (a.ExamID IN (SELECT ID FROM E_Exam WHERE (CourseCertificateID IN (SELECT CourseCertificateID FROM E_Exam AS E_Exam_1 WHERE (ID = " + examid + "))) AND (CategoryID = 1))) AND (b.UserID = " + userid + ")";
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        /// <summary>
        /// 更新补考
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateUserSecTest(TestModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@ExamID", model.ExamID),
                new SqlParameter("@QuestionIDArray", DALUtils.Convert2String(model.QuestionIDArray)),
                new SqlParameter("@CreatedTime", model.CreatedTime)
            };
            string sql = "UPDATE E_Test SET ExamID = @ExamID, QuestionIDArray = @QuestionIDArray, CreatedTime = @CreatedTime WHERE ID = @ID; UPDATE E_TestLog SET StartTime = @CreatedTime, AnswerArray = '', Score = 0, IsOver = 0, SubmitTime = NULL, IsSec = 1 WHERE TestID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取成绩列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ScoreListModel getInternalScoreByCondition(SearchScoreModel searchModel, int pageSize, int pageIndex)
        {
            ScoreListModel listModel = new ScoreListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", searchModel.LoginID),
                new SqlParameter("@UserName", searchModel.UserName),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@FromScore", searchModel.FromScore),
                new SqlParameter("@ToScore", searchModel.ToScore),
                new SqlParameter("@FromDate", searchModel.StartFromDate),
                new SqlParameter("@ToDate", searchModel.StartToDate),
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortScoreModel> list = new List<ShortScoreModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getScoreListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortScoreModel ssm = new ShortScoreModel();
                        //ssm.ID = (int)dr["ID"];
                        ssm.LoginID = dr["LoginID"].ToString();
                        ssm.UserID = (int)dr["ID"];
                        ssm.UserName = dr["UserName"].ToString();
                        ssm.Education = dr["Education"].ToString();
                        //ssm.CourseCertificateName = dr["CourseCertificateName"].ToString();
                        //ssm.Score = (int)dr["Score"];
                        //ssm.TestID = (int)dr["TestID"];
                        //ssm.ExamID = (int)dr["ExamID"];
                        ssm.Gender = (bool)dr["Gender"];
                        //ssm.CategoryID = (int)dr["CategoryID"];
                        list.Add(ssm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取用户课程考试成绩
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDictionary<string, int> GetCourseScore(int userid)
        { 
            IDictionary<string, int> list = new Dictionary<string, int>();
            string sql = "SELECT d.Name, b.Score FROM E_Test AS a INNER JOIN E_TestLog AS b ON a.ID = b.TestID INNER JOIN E_Exam AS c ON a.ExamID = c.ID INNER JOIN E_Dict_Course AS d ON c.CourseCertificateID = d.ID WHERE (c.CategoryID = 1) AND (b.UserID = " + userid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add(dr[0].ToString(), (int)dr[1]);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取用户课程考试成绩
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDictionary<string, int> GetProjectScore(int userid)
        { 
            IDictionary<string, int> list = new Dictionary<string, int>();
            string sql = "SELECT d.Name, b.Score FROM E_Test AS a INNER JOIN E_TestLog AS b ON a.ID = b.TestID INNER JOIN E_Exam AS c ON a.ExamID = c.ID INNER JOIN E_Dict_Certificate AS d ON c.CertificateID = d.ID WHERE (c.CategoryID = 2) AND (b.UserID = " + userid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add(dr[0].ToString(), (int)dr[1]);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取用户输入成绩
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDictionary<string, int> GetInputScore(int userid)
        {
            IDictionary<string, int> list = new Dictionary<string, int>();
            string sql = "SELECT b.Name, a.Score FROM E_Internal_Score AS a INNER JOIN E_Internal_ScoreCategory AS b ON a.CategoryID = b.ID WHERE (a.UserID = " + userid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add(dr[0].ToString(), (int)dr[1]);
                }
                return list;
            }
        }

        #endregion

        #region 公共课

        /// <summary>
        /// 获取公共课考试列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ExamListModel GetInternalPublicExamListByCondition(SearchExamModel searchModel, int pageSize, int pageIndex)
        {
            ExamListModel listModel = new ExamListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", searchModel.Title),
                new SqlParameter("@CategoryID", (int)searchModel.CategoryID),
                new SqlParameter("@CourseCertificateID", searchModel.CourseCertificateID),
                new SqlParameter("@StartFromTime", searchModel.StartFromTime),
                new SqlParameter("@StartToTime", searchModel.StartToTime),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortExamInfoModel> list = new List<ShortExamInfoModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_public_getExamListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortExamInfoModel seim = new ShortExamInfoModel();
                        seim.ID = (int)dr["ID"];
                        seim.CourseCertificateID = (int)dr["CourseCertificateID"];
                        seim.CategoryID = (EnumExamCategory)dr["CategoryID"];
                        seim.Title = dr["Title"].ToString();
                        seim.StartTime = (DateTime)dr["StartTime"];
                        list.Add(seim);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取内训课程考试列表
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<ShortExamInfoModel> GetPublicCourseExamList(int userid)
        {
            IList<ShortExamInfoModel> list = new List<ShortExamInfoModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_public_getCourseExamList", parms))
            {
                while (dr.Read())
                {
                    ShortExamInfoModel model = new ShortExamInfoModel();
                    model.ID = (int)dr["ID"];
                    model.Title = dr["Title"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.EndTime = (DateTime)dr["EndTime"];
                    list.Add(model);
                }
                return list;
            }
        }

        #endregion

        #region 内训 成绩

        /// <summary>
        /// 成绩类别列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetInternalScoreCategoryList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Internal_ScoreCategory";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 添加类别
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int AddScoreCategory(string name)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", name)
            };
            string sql = "INSERT INTO E_Internal_ScoreCategory(Name)VALUES(@Name)";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 更新类别
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public int UpdateScoreCategory(int id, string name)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", id),
                new SqlParameter("@Name", name)
            };
            string sql = "UPDATE E_Internal_ScoreCategory SET Name = @Name WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 检查类别名称是否重复
        /// </summary>
        /// <param name="coursename"></param>
        /// <param name="selfid"></param>
        /// <returns></returns>
        public bool CheckExistCategoryName(string name, int selfid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", name),
                new SqlParameter("@SelfID", selfid)
            };
            string sql = "SELECT ID FROM E_Internal_ScoreCategory WHERE Name = @Name ";
            if (selfid != -1)
            {
                sql += " AND ID <> @SelfID";
            }
            return SqlHelper.Exists(sql, parms);
        }

        /// <summary>
        /// 获取类别名称
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetScoreCategoryInfo(int id)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", id)
            };
            string name = "";
            string sql = "SELECT Name FROM E_Internal_ScoreCategory WHERE ID = @ID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql, parms))
            {
                if (dr.Read())
                {
                    name = dr["Name"].ToString();
                }
                return name;
            }
        }

        /// <summary>
        /// 删除题目
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelScoreCategory(string[] id)
        {
            string sql = "DELETE FROM E_Internal_ScoreCategory WHERE " + DALUtils.GetInCondition("ID", id);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        #endregion

    }
}
